IF OBJECT_ID('dbo.RptSubcontractorsIn') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptSubcontractorsIn
    IF OBJECT_ID('dbo.RptSubcontractorsIn') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptSubcontractorsIn >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptSubcontractorsIn >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptSubcontractorsIn
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 6/15/2007
--
-- Description: Source of Report, "SubcontractorsInReport"
--                  In Insight DB Reporting Services
--
-- Parameters:  
--     @StartDate               
--     @EndDate                 
--     @Institution             
--     @Department              
--     @PrincipalInvestigator        
--     @Sponsor                 
--     @BudgetStatus        
--     @domainUserId            
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptSubcontractorsIn()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 6/15/07     KC        Created
-- 7/20/07     KC        Changed Naming Conventions
-- 7/23/07     KC        New columns on Fact table
-- 8/2/07      KC        Addressed QA Feedback, BudgetDate fix.
-- 8/7/2007    KC        Added '0' for 'ALL' in Params
-- 8/8/07      KC        Made Security Inner Join
-- 8/17/07     KC        Security changed to Cached table
-- 8/24/07     KC        Secondary Sponsor and ProjectDate fixes
-- 8/27/07     KC        Added UDF.PostAwardManager               
-- 10/5/07     KC        Changed to use new tables, AgrAgreement_Info AND
--                       AgrAgreement_Budget
-- 10/10/07    KC        Fix for ProjectFundNumber
-- 12/17/08    KC        New Security function
-- 01/05/08    KC        Security Function Perf Issues: use TempTable
--
-- =======================================================================
CREATE PROCEDURE dbo.RptSubcontractorsIn
     (
     @StartDate               DATETIME,  
     @EndDate                 DATETIME,
     @Institution             VARCHAR(100) = NULL,
     @Department              VARCHAR(100) = NULL,
     @Unit                    VARCHAR(100) = NULL,
     @PrincipalInvestigator   VARCHAR(100) = NULL,          
     @Sponsor                 VARCHAR(100) = NULL,
     @SecondarySponsor        VARCHAR(100) = NULL,
     @BudgetStatus            VARCHAR(100) = NULL,        
     @DomainUserId            INT
     ) 
                                   
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error INT,
               @RowCount INT
               
     -- ALL checking
     IF @Institution IN ('ALL','','0') SET @Institution = NULL
     IF @Department IN ('ALL','','0') SET @Department = NULL
     IF @Unit IN ('ALL','','0') SET @Unit = NULL
     IF @PrincipalInvestigator IN ('ALL','','0') SET @PrincipalInvestigator = NULL
     IF @Sponsor IN ('ALL','','0') SET @Sponsor = NULL
     IF @SecondarySponsor IN ('ALL','','0') SET @SecondarySponsor = NULL
     IF @BudgetStatus IN ('ALL','','0') SET @BudgetStatus = NULL
     
     -- Performance Issues, Get Secured AgreementIds into a temp table
     CREATE TABLE   #tt_Agreements  (AgreementId INT)
     
     INSERT    #tt_Agreements (AgreementId)
     SELECT    AgreementId
     FROM      dbo.fnGetSecurityAgreements(@DomainUserId) 

     -- Result Set
     SELECT    AI.FolderNumber AS ProposalNumber,   
               ISNULL(BUDG.FundNumber,AI.ProjectFundNumber) AS FundNumber,
               AI.PrincipalInvestigator AS PrincipalInvestigator,
               AI.ProposalTitle AS ProposalTitle, 
               AI.Institution AS Institution,
               AI.Department AS Department,
               AI.ChiefCode AS ChiefCode,
               AI.Unit AS Unit,
               AI.SponsorName AS SponsorName,        
               AI.SponsorAgreementID AS SponsorNumber,
               CONVERT(VARCHAR(10),AI.ProjectStartDate,101) AS ProjectStartDate,
               CONVERT(VARCHAR(10),AI.ProjectEndDate,101) AS ProjectEndDate,
               CONVERT(VARCHAR(10),BUDG.BudgetStartDate,101) AS BudgetStartDate,
               CONVERT(VARCHAR(10),BUDG.BudgetEndDate,101) AS BudgetEndDate,
               AI.MasterProposalStatus AS BudgetStatus,
               AI.SecondarySponsor AS SecondarySponsor,              -- Existence indicates Sub-IN?
               PROJ.ProjectDirectCost AS DirectCostforProjectPeriod,
               BUDG.BudgetDirectCost AS DirectCostforBudgetPeriod,
               PROJ.ProjectIndirectCost AS FAforProjectPeriod,
               BUDG.BudgetIndirectCost AS FAforBudgetPeriod,
               AI.InfoEdPropNumber AS InfoEdPropNumber,
               AI.PrincipalInvestigatorId AS PrincipalInvestigatorId,
               ISNULL(AI.PostAwardManagerName,' Post Award Manager Not Assigned') AS PostAwardManager,
               AI.SponsorId AS SponsorId,
               AI.ResourceId AS AgrResourceId,
               AI.AgreementId AS AgreemendId
     FROM      dbo.AgrAgreement_Info AI INNER JOIN 
               
               -- We need Project Dates, which are over the life of the Agreement
               dbo.vwAgreementProjectBudgets PROJ ON AI.AgreementId = PROJ.AgreementId INNER JOIN

               -- We need only the latest BudgetYear and BudgetDates for each 
               -- FolderNumber within the search date params.
               dbo.fnGetPeriodBudget(@StartDate, @EndDate) BUDG ON AI.AgreementId = BUDG.AgreementId INNER JOIN

               -- Security Issues: Not all Users can see all Agreements!
               #tt_Agreements AUTH ON AI.AgreementId = AUTH.AgreementId
               
     WHERE     (AI.InstrumentType = 'Subcontract') AND          -- Indicates "SubIN"!
               (AI.ProjectStartDate BETWEEN @StartDate AND @EndDate) AND
               (@Institution IS NULL OR AI.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND
               (@Department IS NULL OR AI.DepartmentId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Department))) AND
               (@Unit IS NULL OR AI.UnitId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Unit))) AND
               (@PrincipalInvestigator IS NULL OR AI.PrincipalInvestigatorId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PrincipalInvestigator ))) AND
               (@Sponsor IS NULL OR AI.SponsorId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @Sponsor ))) AND
               (@BudgetStatus IS NULL OR AI.BudgetPeriodStatus IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode( @BudgetStatus ))) AND
               (@SecondarySponsor IS NULL OR AI.SecondarySponsor = @SecondarySponsor )
     ORDER BY AI.SponsorName, AI.Institution, AI.Department

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     
     DROP TABLE #tt_Agreements
     
     IF @Error!=0
          RETURN -1
          
     -- Return Success
     RETURN 0

     
END
go

IF OBJECT_ID('dbo.RptSubcontractorsIn') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptSubcontractorsIn >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptSubcontractorsIn >>>'
go

GRANT EXECUTE ON dbo.RptSubcontractorsIn TO InsightReport,Insight
go